﻿using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;
using System.Data.SqlClient;

namespace MembershipCustomizado
{

    public sealed class MembershipProviderCustom : MembershipProvider
    {
       
        private int newPasswordLength = 8;
        private string eventSource = "MembershipProviderCustom";
        private string eventLog = "Application";
        private string exceptionMessage = "Ocoreu uma execeção. Por favor check o Log de Eventos.";
        //   private string connectionString;

        private string connectionString = WebConfigurationManager.ConnectionStrings["Login"].ConnectionString;
        private MachineKeySection machineKey;

        private bool pWriteExceptionsToEventLog;

        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }

        private string pApplicationName = "SisAfast";
        private bool pEnablePasswordReset;
        private bool pEnablePasswordRetrieval;
        private bool pRequiresQuestionAndAnswer;
        private bool pRequiresUniqueEmail;
        private int pMaxInvalidPasswordAttempts;
        private int pPasswordAttemptWindow;
        private MembershipPasswordFormat pPasswordFormat;

        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        public override bool EnablePasswordReset
        {
            get { return pEnablePasswordReset; }
        }

        public override bool EnablePasswordRetrieval
        {
            get { return pEnablePasswordRetrieval; }
        }

        public override bool RequiresQuestionAndAnswer
        {
            get { return pRequiresQuestionAndAnswer; }
        }

        public override bool RequiresUniqueEmail
        {
            get { return pRequiresUniqueEmail; }
        }
        
        public override int MaxInvalidPasswordAttempts
        {
            get { return pMaxInvalidPasswordAttempts; }
        }

        public override int PasswordAttemptWindow
        {
            get { return pPasswordAttemptWindow; }
        }

        public override MembershipPasswordFormat PasswordFormat
        {
            get { return pPasswordFormat; }
        }

        private int pMinRequiredNonAlphanumericCharacters;

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return pMinRequiredNonAlphanumericCharacters; }
        }

        private int pMinRequiredPasswordLength;

        public override int MinRequiredPasswordLength
        {
            get { return pMinRequiredPasswordLength; }
        }

        private string pPasswordStrengthRegularExpression;

        public override string PasswordStrengthRegularExpression
        {
            get { return pPasswordStrengthRegularExpression; }
        }



        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            if (config == null)
                throw new ArgumentNullException("config");

            if (name == null || name.Length == 0)
                name = "MembershipProviderCustom";

            if (String.IsNullOrEmpty(config["Descricao"]))
            {
                config.Remove("Descricao");
                config.Add("Descricao", " Membership provider Customizado");
            }

            base.Initialize(name, config);



            pApplicationName = GetConfigValue(config["applicationName"],
                                                        System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
            pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
            pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
            pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
            pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
            pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
            pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
            pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
            pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
            pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"));

            string temp_format = config["passwordFormat"];
            if (temp_format == null)
            {
                temp_format = "Hashed";
            }

            switch (temp_format)
            {
                case "Hashed":
                    pPasswordFormat = MembershipPasswordFormat.Hashed;
                    break;
                case "Encrypted":
                    pPasswordFormat = MembershipPasswordFormat.Encrypted;
                    break;
                case "Clear":
                    pPasswordFormat = MembershipPasswordFormat.Clear;
                    break;
                default:
                    throw new ProviderException("Password format not supported.");
            }

            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
            {
                throw new ProviderException("String de Conexão não pode ser em branco.");
            }

            connectionString = ConnectionStringSettings.ConnectionString;

            Configuration cfg =
              WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");

            if (machineKey.ValidationKey.Contains("AutoGenerate"))
                if (PasswordFormat != MembershipPasswordFormat.Clear)
                    throw new ProviderException("Senhas Hashed ou Encriptadas  " +
                                                "Não são suportadas para geração automática de chaves.");
        }

        private string GetConfigValue(string configValue, string defaultValue)
        {
            if (String.IsNullOrEmpty(configValue))
                return defaultValue;

            return configValue;
        }

        private void WriteToEventLog(Exception e, string action)
        {
            EventLog log = new EventLog();
            log.Source = eventSource;
            log.Log = eventLog;

            string message = "An exception occurred communicating with the data source.\n\n";
            message += "Action: " + action + "\n\n";
            message += "Exception: " + e.ToString();

            log.WriteEntry(message);
        }

        static string GetMd5Hash(MD5 md5Hash, string input)
        {

            // Convert the input string to a byte array and compute the hash.
            byte[] data = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input));

            // Create a new Stringbuilder to collect the bytes
            // and create a string.
            StringBuilder sBuilder = new StringBuilder();

            // Loop through each byte of the hashed data 
            // and format each one as a hexadecimal string.
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }

            // Return the hexadecimal string.
            return sBuilder.ToString();
        }

        static bool VerifyMd5Hash(MD5 md5Hash, string input, string hash)
        {
            // Hash the input.
            string hashOfInput = GetMd5Hash(md5Hash, input);

            // Create a StringComparer an compare the hashes.
            StringComparer comparer = StringComparer.OrdinalIgnoreCase;

            if (0 == comparer.Compare(hashOfInput, hash))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private bool CheckPassword(string password, string dbpassword)
        {
            string pass1 = password;
            string pass2 = dbpassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Encrypted:
                    pass2 = UnEncodePassword(dbpassword);
                    break;
                case MembershipPasswordFormat.Hashed:
                    pass1 = EncodePassword(password);
                    break;
                default:
                    break;
            }

            if (pass1 == pass2)
            {
                return true;
            }

            return false;
        }

        private string EncodePassword(string password)
        {
            string encodedPassword = password;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    encodedPassword =
                      Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    HMACSHA1 hash = new HMACSHA1();
                    hash.Key = HexToByte(machineKey.ValidationKey);
                    encodedPassword =
                      Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                    break;
                default:
                    throw new ProviderException("Formato de Senha não suportado.");
            }

            return encodedPassword;
        }

        private string UnEncodePassword(string encodedPassword)
        {
            string password = encodedPassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    password =
                      Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    throw new ProviderException("Impossível decodificar uma senha de hash.");
                default:
                    throw new ProviderException("Formato de Senha não suportado.");
            }

            return password;
        }


        private byte[] HexToByte(string hexString)
        {
            byte[] returnBytes = new byte[hexString.Length / 2];
            for (int i = 0; i < returnBytes.Length; i++)
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            return returnBytes;
        }

        private MembershipUser GetUserFromReader(SqlDataReader reader)
        {
            object providerUserKey = reader.GetValue(0);
            string username = reader.GetString(1);
            string email = reader.GetString(2);

            string passwordQuestion = "";
            if (reader.GetValue(3) != DBNull.Value)
                passwordQuestion = reader.GetString(3);

            string comment = "";
            if (reader.GetValue(4) != DBNull.Value)
                comment = reader.GetString(4);

            bool isApproved = reader.GetBoolean(5);
            bool isLockedOut = reader.GetBoolean(6);
            DateTime creationDate = reader.GetDateTime(7);

            DateTime lastLoginDate = new DateTime();
            if (reader.GetValue(8) != DBNull.Value)
                lastLoginDate = reader.GetDateTime(8);

            DateTime lastActivityDate = reader.GetDateTime(9);
            DateTime lastPasswordChangedDate = reader.GetDateTime(10);

            DateTime lastLockedOutDate = new DateTime();
            if (reader.GetValue(11) != DBNull.Value)
                lastLockedOutDate = reader.GetDateTime(11);

            MembershipUser u = new MembershipUser(this.Name,
                                                  username,
                                                  providerUserKey,
                                                  email,
                                                  passwordQuestion,
                                                  comment,
                                                  isApproved,
                                                  isLockedOut,
                                                  creationDate,
                                                  lastLoginDate,
                                                  lastActivityDate,
                                                  lastPasswordChangedDate,
                                                  lastLockedOutDate);

            return u;
        }
        
        public override bool ChangePasswordQuestionAndAnswer(string username,
                     string password,
                     string newPwdQuestion,
                     string newPwdAnswer)
        {
            throw new ProviderException("Não é Possivel Trocar a Senha");
        }

        public override MembershipUser CreateUser(string username,
                 string password,
                 string email,
                 string passwordQuestion,
                 string passwordAnswer,
                 bool isApproved,
                 object providerUserKey,
                 out MembershipCreateStatus status)
        {
            throw new ProviderException("Não é Possivel Criar Usuário");        
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            throw new ProviderException("Não é Possivel Apagar Usuário");            
        }

        public override string GetPassword(string username, string answer)
        {
            throw new ProviderException("Não é Possivel Trocar a Senha");           
        }

        public override bool UnlockUser(string username)
        {
            throw new ProviderException("Não é Possivel Desbloquear o Usuário");        
        }

        public override string ResetPassword(string username, string answer)
        {
            throw new ProviderException("Não é Possivel Trocar a Senha");
        }

        public override void UpdateUser(MembershipUser user)
        {
            throw new ProviderException("Não é Possivel Alterar Usuário.");
        }

        public override bool ChangePassword(string usuario, string oldPwd, string newPwd)
        { 
            SqlConnection conn = new SqlConnection(connectionString);
           
            try
            {
                MD5 md5Hash = MD5.Create();

                string poldPwd = GetMd5Hash(md5Hash, oldPwd);

                poldPwd = poldPwd.ToUpper();

                string  poldPwdDatabase = GetPassword(usuario);

                if (CheckPassword(poldPwd, poldPwdDatabase))
                {
                    string pnewPwd = GetMd5Hash(md5Hash, newPwd);

                    pnewPwd = pnewPwd.ToUpper();

                    conn.Open();
                    SqlCommand cmd = new SqlCommand("Usp_Trocar_Senha_por_Nome_Usuario", conn);

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@pUsuario", usuario);
                    cmd.Parameters.AddWithValue("@pSenhaNova", pnewPwd);

                    cmd.ExecuteNonQuery();

                    return true;
                }
                else
                {
                    return false;
                }
            }           
            catch (SqlException e)
            {                
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePassword(bool, Senha)");

                    throw new ProviderException(exceptionMessage);

                }
                else
                {
                    throw e;
                }                
            }
            finally
            {
                conn.Close();
            }    
        }

        public string GetPassword(string username)
        {
            SqlConnection conn = new SqlConnection(connectionString);

         
            SqlCommand cmd = new SqlCommand("Usp_Usuario_ObterSenhaDoUsuario", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Usuario", username);
            cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);

            SqlDataReader reader = null;          
            string senha = "";

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    senha = reader["Senha"].ToString();                   
                }
          
                reader.Close();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetPassword(String, Senha)");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {                
                conn.Close();
            }
            return senha;
        }
        
        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM Usuario " +
                                            "INNER JOIN AplicacaoXUsuario AU ON (U.Id_Usuario = AU.Id_Usuario) " +
                                            "INNER JOIN Aplicacao A ON (AU.Id_Aplicacao = A.Id_Aplicacao) " +
                                              "WHERE upper(A.Aplicacao) = @ApplicationName", conn);
            cmd.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 255).Value = ApplicationName;

            MembershipUserCollection users = new MembershipUserCollection();

            SqlDataReader reader = null;
            totalRecords = 0;

            try
            {
                conn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0) { return users; }

                cmd.CommandText = " SELECT U.Id_Usuario, U.Nome, U.Sobrenome, U.Email," +
                       " U.Usuario, U.Senha, U.Status, U.Dt_Inclusao, U.QuestaoSenha," +
                       " U.RespostaSenha, U.EstaAprovada, U.UltimaAtividadeData, " +
                       " U.UltimoLoginData, U.UltimaDataMudancaSenha, U.IsOnLine, U.IsLockedOut, " +
                       " U.LastLockedOutDate, U.QteTentativasFracassadasLogin, U.QteTentativasFracassadasLoginData,  " +
                       " U.QteTentativasRespostasLogin , U.QteTentativasRespostasLoginData  " +
                       " FROM Usuario U" +
                       "INNER JOIN AplicacaoXUsuario AU ON (U.Id_Usuario = AU.Id_Usuario) " +
                       "INNER JOIN Aplicacao A ON (AU.Id_Aplicacao = A.Id_Aplicacao) " +
                       " WHERE upper(A.Aplicacao) = @ApplicationName" +
                       " ORDER BY Usuario Asc";

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex) { cmd.Cancel(); }

                    counter++;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetAllUsers ");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            return users;
        }

        public override int GetNumberOfUsersOnline()
        {

            TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);


            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Usuario_ObterNumeroDeUsuariosOnline", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
            cmd.Parameters.AddWithValue("@CompareDateTime", compareTime);
       

            int numOnline = 0;

            try
            {
                conn.Open();

                numOnline = (int)cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNumberOfUsersOnline");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return numOnline;
        }

        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Usuario_ObterUsuarioPorNome", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Usuario", username);
            cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
          

            MembershipUser u = null;
            SqlDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    u = GetUserFromReader(reader);                   
                }

            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser(String, Boolean)");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }

                conn.Close();
            }

            return u;
        }

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Usuario_ObterUsuarioPorId", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id_usuario", providerUserKey);
            cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);            

            MembershipUser u = null;
            SqlDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    u = GetUserFromReader(reader);                   
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser(Object, Boolean)");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }

                conn.Close();
            }

            return u;
        }

        public override string GetUserNameByEmail(string email)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Usuario_ObterUsuarioPorEmail", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
            cmd.Parameters.AddWithValue("@Email", email);
          

            string username = "";

            try
            {
                conn.Open();

                username = (string)cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNumberOfUsersOnline");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            if (username == null)
                username = "";

            return username;
        }

        public override bool ValidateUser(string username, string password)
        {
            bool isValid = false;

            MD5 md5Hash = MD5.Create();

            string ppassword = GetMd5Hash(md5Hash, password);

            ppassword = ppassword.ToUpper();


            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("usp_Login", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Usuario", username);
            cmd.Parameters.AddWithValue("@Senha", ppassword);
            cmd.Parameters.AddWithValue("@Id_Aplicacao", 4);


            SqlDataReader reader = null;
            bool isApproved = false;
            string pwd = "";

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    pwd = reader["Senha"].ToString();
                    isApproved = (bool)reader["Status"];
                }
                else
                {
                    UpdateFailureCount(username, "password");
                    return false;
                }

                reader.Close();

                if (CheckPassword(ppassword, pwd))
                {
                    if (isApproved)
                    {
                        isValid = true;
                    }
                }
                else
                {
                    conn.Close();
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ValidateUser");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            return isValid;
        }

        private void UpdateFailureCount(string username, string failureType)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT QteTentativasFracassadasLogin, " +
                                              "  QteTentativasFracassadasLoginData, " +
                                              "  QteTentativasRespostasLogin, " +
                                              "  QteTentativasRespostasLoginData " +
                                              "  FROM Usuario" +
                                              "  WHERE Usuario = @Username", conn);


            cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = username;


            SqlDataReader reader = null;
            DateTime windowStart = new DateTime();
            int failureCount = 0;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();

                    if (failureType == "password")
                    {
                        failureCount = reader.GetInt32(0);
                        windowStart = reader.GetDateTime(1);
                    }

                    if (failureType == "passwordAnswer")
                    {
                        failureCount = reader.GetInt32(2);
                        windowStart = reader.GetDateTime(3);
                    }
                }

                reader.Close();

                DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);

                if (failureCount == 0 || DateTime.Now > windowEnd)
                {
                    // First password failure or outside of PasswordAttemptWindow. 
                    // Start a new password failure count from 1 and a new window starting now.

                    if (failureType == "password")
                        cmd.CommandText = "UPDATE Usuario " +
                                         "  SET QteTentativasFracassadasLogin = @Count, " +
                                         "      QteTentativasFracassadasLoginData = @WindowStart " +
                                         "  WHERE Usuario = @Username";

                    if (failureType == "passwordAnswer")
                        cmd.CommandText = "UPDATE Usuario " +
                                          "  SET QteTentativasRespostasLogin = @Count, " +
                                          "      QteTentativasRespostasLoginData = @WindowStart " +
                                          "  WHERE Usuario = @Username";

                    cmd.Parameters.Clear();

                    cmd.Parameters.Add("@Count", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@WindowStart", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;

                    if (cmd.ExecuteNonQuery() < 0)
                        throw new ProviderException(" Falha na Atualização do Qte de falhas na inicializações");
                }
                else
                {
                    if (failureCount++ >= MaxInvalidPasswordAttempts)
                    {
                        // Password attempts have exceeded the failure threshold. Lock out
                        // the user.

                        cmd.CommandText = "UPDATE Usuario " +
                                          "  SET IsLockedOut = @IsLockedOut, LastLockedOutDate = @LastLockedOutDate " +
                                          "  WHERE Usuario = @Username";
                        cmd.Parameters.Clear();

                        cmd.Parameters.Add("@IsLockedOut", SqlDbType.Bit).Value = true;
                        cmd.Parameters.Add("@LastLockedOutDate", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;

                        if (cmd.ExecuteNonQuery() < 0)
                            throw new ProviderException("Falha na Trava do Usuário.");
                    }
                    else
                    {
                        // Password attempts have not exceeded the failure threshold. Update
                        // the failure counts. Leave the window the same.

                        if (failureType == "password")
                            cmd.CommandText = "UPDATE Usuario " +
                                                "  SET QteTentativasFracassadasLogin = @Count " +
                                                "  WHERE Usuario = @Username";

                        if (failureType == "passwordAnswer")
                            cmd.CommandText = "UPDATE Usuario " +
                                                        "  SET QteTentativasRespostasLogin =  ? " +
                                                        "  WHERE Usuario U = @Username ";

                        cmd.Parameters.Clear();

                        cmd.Parameters.Add("@Count", SqlDbType.Int).Value = failureCount;
                        cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;

                        if (cmd.ExecuteNonQuery() < 0)
                            throw new ProviderException("Falha na Atualização do Qte de falhas na inicializações");
                    }
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UpdateFailureCount");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }
        }

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM Users " +
                      "WHERE Username LIKE @UsernameSearch AND ApplicationName = @ApplicationName", conn);
            cmd.Parameters.Add("@UsernameSearch", SqlDbType.VarChar, 255).Value = usernameToMatch;
            cmd.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 255).Value = pApplicationName;

            MembershipUserCollection users = new MembershipUserCollection();

            SqlDataReader reader = null;

            try
            {
                conn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0) { return users; }

                cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
                  " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
                  " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
                  " FROM Users " +
                  " WHERE Username LIKE @UsernameSearch AND ApplicationName = ApplicationName " +
                  " ORDER BY Username Asc";

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex) { cmd.Cancel(); }

                    counter++;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersByName");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }

                conn.Close();
            }

            return users;
        }

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM Users " +
                                              "WHERE Email LIKE @EmailSearch AND ApplicationName = @ApplicationName", conn);
            cmd.Parameters.Add("@EmailSearch", SqlDbType.VarChar, 255).Value = emailToMatch;
            cmd.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 255).Value = ApplicationName;

            MembershipUserCollection users = new MembershipUserCollection();

            SqlDataReader reader = null;
            totalRecords = 0;

            try
            {
                conn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0) { return users; }

                cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
                         " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
                         " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
                         " FROM Users " +
                         " WHERE Email LIKE @EmailSearch AND ApplicationName = @ApplicationName " +
                         " ORDER BY Username Asc";

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex) { cmd.Cancel(); }

                    counter++;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersByEmail");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }

                conn.Close();
            }

            return users;
        }

        public string GetNomeCompleto(string username)
        {
            /*  ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["Login"];

              if (connectionStringSettings == null || connectionStringSettings.ConnectionString.Trim() == "")
              {
                  throw new ProviderException("Connection string cannot be blank.");
              }

              string connStr = connectionStringSettings.ConnectionString;

              SqlConnection conn = new SqlConnection(connStr);*/

            SqlConnection conn = new SqlConnection(connectionString);

            String _username = "";

            SqlCommand cmd = new SqlCommand("usp_GetNomeCompleto", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Usuario", username);

            SqlDataReader dataReader = null;
            try
            {
                conn.Open();

                dataReader = cmd.ExecuteReader();

                if (dataReader.HasRows)
                {
                    dataReader.Read();
                    _username = dataReader["NomeCompleto"].ToString();
                }
                return _username;
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNomeCompleto ");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (dataReader != null)
                { dataReader.Close(); }
                conn.Close();
            }
            return _username;
        }

        /*
        public string GetSetoresPorUsuario(string username)
        {
            bool flag;
            string s1;

            SqlConnection conn = new SqlConnection(connectionString);
            string _username = "";
            SqlCommand cmd = new SqlCommand("Usp_GetSetoresPorUsuario", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Usuario", username);
            SqlDataReader dataReader = null;
            try
            {
                try
                {
                    conn.Open();
                    dataReader = cmd.ExecuteReader();
                    flag = !dataReader.HasRows;
                    if (!flag)
                    {
                        dataReader.Read();
                        _username = dataReader["Id_Setor"].ToString();
                    }
                    s1 = _username;
                    goto label_0;
                }
                catch (SqlException e)
                {
                    flag = !WriteExceptionsToEventLog;
                    if (!flag)
                    {
                        WriteToEventLog(e, "GetSetoresPorUsuario ");
                        throw new ProviderException(exceptionMessage);
                    }
                    throw e;
                }
            }
            finally
            {
                flag = dataReader == null;
                if (!flag)
                    dataReader.Close();
                conn.Close();
            }
            return s1;
        }
        */
    }
}
